﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using EmoneyWeiXinCommon;
using EmoneyWeiXinEntity;
using System.Data.SqlClient;
namespace EmoneyWeiXinDAL
{
    public class UserDAL
    {
        public List<UserEntity> getUserAll(string username, string userpwd)
        {
            string sql = "";
            SQLTools tools = new SQLTools();
            if (string.IsNullOrEmpty(username) && string.IsNullOrEmpty(userpwd))
            {
                sql = "select id, loginName, loginpwd, Username, createtime, updatetime, isvalid, remark, IPaddress, Ipnum from users order by updatetime desc";
            }
            else
            {
                if (string.IsNullOrEmpty(userpwd))
                {
                    sql = "select id, loginName, loginpwd, Username, createtime, updatetime, isvalid, remark, IPaddress, Ipnum from users where loginname=@username and isvalid=1 order by updatetime desc";
                    tools.AddSqlParameter("username", SqlDbType.VarChar, 20, username);
                }
                else
                {
                    sql = "select id, loginName, loginpwd, Username, createtime, updatetime, isvalid, remark, IPaddress, Ipnum from users where loginname=@username and loginpwd=@userpwd and isvalid=1 order by updatetime desc";
                    tools.AddSqlParameter("username", SqlDbType.VarChar, 20, username);
                    tools.AddSqlParameter("userpwd", SqlDbType.VarChar, 20, userpwd);
                }
            }
            return SQLHelper.GetDataByColumnsLst<UserEntity>(Config.EmoneyWeiXin, sql, tools.GetSqlParameters());
        }

        public List<UserEntity> getUserList(int pindex, int psize, out int total)
        {
            SqlParameter[] parameters = new SqlParameter[] {
            new SqlParameter("@pindex",pindex),
            new SqlParameter("@psize", psize),
            new SqlParameter("@total",0)
            };
            parameters[2].Direction = ParameterDirection.Output;
            List<UserEntity> result = SQLHelper.GetProcedureDataByColumnsLst<UserEntity>(Config.EmoneyWeiXin, "sp_user_Select", parameters);
            total = Convert.ToInt32(parameters[2].Value);
            return result;
        }
        public List<UserEntity> getUserListByID(int id)
        {
            SqlParameter[] parameters = new SqlParameter[] {
            new SqlParameter("@id",id)
            };
            List<UserEntity> result = SQLHelper.GetProcedureDataByColumnsLst<UserEntity>(Config.EmoneyWeiXin, "sp_user_SelectByID", parameters);
            return result;
        }
        public int UpdateUser(UserEntity obj)
        {
            SqlParameter[] parameters = new SqlParameter[] {
            new SqlParameter("@id" , obj.id),
            new SqlParameter("@loginName", obj.loginName),
            new SqlParameter("@loginpwd", obj.loginpwd),
            new SqlParameter("@Username", obj.Username),
            new SqlParameter("@createtime", obj.createtime),
            new SqlParameter("@updatetime", obj.updatetime),
            new SqlParameter("@isvalid", obj.isvalid),
            new SqlParameter("@remark", obj.remark),
            new SqlParameter("@IPaddress" , obj.IPaddress),
            new SqlParameter("@Ipnum" , obj.Ipnum),
            new SqlParameter("@csid" , obj.csid),
            new SqlParameter("@leftmenuids" , obj.leftmenuids),
            new SqlParameter("@weixinids" , obj.weixinids),
            new SqlParameter("@menuids" , obj.menuids),
            new SqlParameter("@operates" , obj.operates),
            new SqlParameter("@keyid",0)
            };
            parameters[15].Direction = ParameterDirection.Output;
            int result = SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, CommandType.StoredProcedure, "sp_users_Update", parameters);
            int ir = Convert.ToInt32(parameters[15].Value);
            return ir;
        }

        public int UpdateLeftmemu_operate(int op, int userid, int leftmemu_id, string operate_id)
        {
            SqlParameter[] parameters = new SqlParameter[] {
            new SqlParameter("@op" , op),
            new SqlParameter("@userid", userid),
            new SqlParameter("@leftmemu_id", leftmemu_id),
            new SqlParameter("@operate_id", operate_id)
            };
            int result = SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, CommandType.StoredProcedure, "sp_leftmemu_operate_update", parameters);
            return result;
        }
        public int DeleteUser(int id)
        {
            SqlParameter[] parameters = new SqlParameter[] {  
            new SqlParameter("@id", id)};
            int result = SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, CommandType.StoredProcedure, "sp_users_Delete", parameters);
            return result;
        }

        /// <summary>
        /// 根据通行证id获取用户
        /// </summary>
        /// <param name="txzId"></param>
        /// <returns></returns>
        public UserEntity GetUserEntityByTxzId(int txzId)
        {
            string sql = "SELECT  top 1 * FROM users WHERE txzUserId=@txtId";
            SqlParameter[] parameters = new SqlParameter[] {  
            new SqlParameter("@txtId", txzId)};
          UserEntity result = SQLHelper.GetDataByColumns<UserEntity>(Config.EmoneyWeiXin,sql, parameters);
          return result;
        }
    }
}
